﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Sql;
using System.Data.SqlClient;

namespace LMIS_Application
{
    public class Inv
    {
        public const string AVAILABLE = "1";
        public const string UNAVAILABLE = "0";
        
        private int invID;
        private int status;
        private int bkid;
        private int memberid;
        private DateTime CDate;
        private DateTime DDate;

        public int? InvID
        {
            get
            {
                return invID;
            }
            private set
            {
                if (value == null)
                {
                    throw new ArgumentNullException("InventoryID value cannot be null!");
                }

                invID = (int)value;
            }
        }

        public int? Status
        {
            get
            {
                return status;
            }
            private set
            {
                if (value == null)
                {
                    throw new ArgumentNullException("Status value cannot be null!");
                }

                status = (int)value;
            }
        }

        public int? BookId
        {
            get
            {
                return bkid;
            }
            private set
            {
                if (value == null)
                {
                    throw new ArgumentNullException("BookId value cannot be null!");
                }

                bkid = (int)value;
            }
        }
        public int? MemberID
        {
            get
            {
                return memberid;
            }
            private set
            {
                if (value == null)
                {
                    throw new ArgumentNullException("MemberID value cannot be null!");
                }

                memberid = (int)value;
            }
        }

        public DateTime CheckoutDate
        {
            get
            {
                return CDate;
            }
            private set
            {
                if (value == null)
                {
                    throw new ArgumentNullException("CheckoutDate value cannot be null!");
                }

                CDate = (DateTime) value;
            }
        }

        public DateTime DueDate
        {
            get
            {
                return DDate;
            }
            private set
            {
                if (value == null)
                {
                    throw new ArgumentNullException("DueDate value cannot be null!");
                }

                DDate = (DateTime)value;
            }
        }

        public void loadInventory(int bkCopyID)
        {
            //load book details into the class
            string command = "SELECT * from Inventory WHERE InventoryId='" + bkCopyID + "'";

            using (SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection())
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(command, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "Inv");

                int rowCount = ds.Tables["Inv"].Rows.Count;

                if (rowCount > 0)
                {
                    //get the first row
                    DataRow dr = ds.Tables["Inv"].Rows[0];
                    this.invID = Convert.ToInt32(dr["InventoryId"]);
                    this.status = Convert.ToInt32(dr["Status"]);
                    this.BookId = Convert.ToInt32(dr["BookId"]);
                    try
                    {
                        this.memberid = Convert.ToInt32(dr["MemberId"]);
                        this.CDate = Convert.ToDateTime(dr["CheckoutDate"]);
                        this.DDate = Convert.ToDateTime(dr["DueDate"]);
                    }
                    catch (Exception e)
                    {
                        conn.Close();
                    }
                    conn.Close();
                }
            }
        }

        public int addBookCopy(int bookID)
        {
            this.bkid = bookID;

            //save them to sql
            string command = "INSERT INTO Inventory(Status, BookId) " +
                                "VALUES('1','" + bookID + "')";

            SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection();
            SqlCommand sc = new SqlCommand();
            sc.Connection = conn;
            sc.CommandText = command;
            sc.Connection.Open();
            Int32 rowsAffected = -1;
            try
            {
                rowsAffected = (Int32)sc.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                sc.Connection.Close();
                return -1;
            }

            sc.Connection.Close();

            if (rowsAffected >= 1)
            {
                command = "SELECT        TOP (1) InventoryId " +
                                 "FROM            Inventory " +
                                 "WHERE        (Status = 1) AND (BookId = " + this.bkid + ") " +
                                 "ORDER BY InventoryId DESC";

                conn = (SqlConnection)DbHelper.GetDatabaseConnection();
                sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = command;
                sc.Connection.Open();
                Int32 invId = -1;
                invId = (Int32)sc.ExecuteScalar();
                sc.Connection.Close();

                return invId;
            }
            else
            {
                return -1;
            }

        }

        public bool editBookCopy(int inventoryID, int Status, int BookId)
        {
            this.bkid = BookId;
            this.status = Status;

            //save them to sql
            String command = "UPDATE Inventory " +
                             "SET Status='" + Status + "',BookId='" + BookId + "' " +
                             "WHERE InventoryId = '" + inventoryID + "'";

            SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection();
            SqlCommand sc = new SqlCommand();
            sc.Connection = conn;
            sc.CommandText = command;
            sc.Connection.Open();
            Int32 rowsAffected = -1;
            try
            {

                rowsAffected = (Int32)sc.ExecuteNonQuery();
                sc.Connection.Close();

                if (rowsAffected >= 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public bool Checkout(Member m, DateTime dueDate)
        {
            bool success = false;

            string command = "UPDATE Inventory SET MemberId='" + m.MemberID + "', Status='"+Inv.UNAVAILABLE+"', CheckoutDate='" + DateTime.Now + "', DueDate='" + dueDate + "' WHERE InventoryId='" + this.InvID + "'";

            using (SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(command, conn);
                int rowsAffected = cmd.ExecuteNonQuery();
                success = rowsAffected > 0;
            }

            return success;

        }

        public bool Checkin()
        {
            bool success = false;

            string command = "UPDATE Inventory SET MemberId=NULL, Status='" + Inv.AVAILABLE + "', CheckoutDate=NULL, DueDate=NULL WHERE InventoryId='" + this.InvID + "'";

            using (SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(command, conn);
                int rowsAffected = cmd.ExecuteNonQuery();
                success = rowsAffected > 0;
            }

            return success;
        }

        public static Inv GetFirstAvailableCopy(int bookId)
        {
            string command = "SELECT InventoryId from Inventory WHERE BookId='" + bookId.ToString() + "' AND Status='" + Inv.AVAILABLE + "'";
            Inv bookCopy = new Inv();
            int invID = -1;

            using (SqlConnection conn = (SqlConnection)DbHelper.GetDatabaseConnection())
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(command, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "Inventory");

                int rowCount = ds.Tables["Inventory"].Rows.Count;

                if (rowCount > 0)
                {
                    //get the first row
                    DataRow dr = ds.Tables["Inventory"].Rows[0];
                    invID = Convert.ToInt32(dr["InventoryId"]);
                }

                if (invID != -1)
                {
                    bookCopy.loadInventory(invID);
                    return bookCopy;
                }
                else
                {
                    return null; //if null, there are no copies available...
                }
            }
        }
    
    }
}
